/*********************************************************************************
 *      Copyright:  (C) 2022 South-Central Minzu University
 *                  All rights reserved.
 *
 *       Filename:  sqliteall.c
 *    Description:  
 *                 
 *        Version:  1.0.0(16/04/22)
 *         Author:  zhangkang <2980640573@qq.com>
 *      ChangeLog:  1, Release initial version on "16/04/22 14:26:54"
 *                 
 ********************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>


#include "sqlite3.h"
#include "logger.h"


char         table_name[16]={"cli_temp"};
char         db_name[16]={"temp_cli.db"};


int					             sqlret = 0;
sqlite3 			          *db;
char 				            *zErrMsg = NULL;
int 	                   maxid = 0;
char                     buf_send[512];

int sqlite_getdata_callback(void *data, int argc, char **argv, char **azColName)
{
    memset(buf_send,0,sizeof(buf_send));
	  snprintf(buf_send, sizeof(buf_send),"%s/%s/%s",argv[1], argv[2], argv[3]);
	  log_info("buf_send:%s\n", buf_send);
	  return 0;
}

int sqlite_getidmax_callback(void *NotUsed, int argc, char **argv, char **azColName)
{
    maxid = atoi(argv[0]);
	  return 0;
}

int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
   int i;
   for(i=0; i<argc; i++)
   {
      log_info("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   return 0;
}

int sqlite_init(char * db_name,char * table_name)      
{
    char buf_table[100];
    
            /* Open database */
    sqlret=sqlite3_open(db_name,&db);
    
    if( sqlret != SQLITE_OK )
    {
        log_error("Error open database: %s\n", sqlite3_errmsg(db));
        sqlite3_free(zErrMsg);
    }
    else
    {
        log_info("successfully opend database\n");
    }
    
                        /*Creat table*/    
    memset(buf_table,0,sizeof(buf_table));
    snprintf(buf_table, sizeof(buf_table),"CREATE TABLE %s(ID INT PRIMARY KEY, SN CHAR(20),DATIME CHAR(100),TEMPERATURE CHAR(50));", table_name);
  	sqlret = sqlite3_exec(db, buf_table, callback, 0, &zErrMsg);
	  if(sqlret != SQLITE_OK)
	  {
        log_warn("create table error:%s\n", zErrMsg);
		    sqlite3_free(zErrMsg);
	  }
	  else
	  {
		    log_info( "Table created successfully!\n");
    }
    return 0;
    
}

int sqlite_getidmax(char * table_name)     
{ 
    char buf_idmax[128];
    
    
    memset(buf_idmax,0,sizeof(buf_idmax));
    snprintf(buf_idmax,sizeof(buf_idmax),"SELECT COUNT(*) from %s", table_name);
    sqlret=sqlite3_exec(db, buf_idmax, sqlite_getidmax_callback, 0, &zErrMsg);
    
    if( sqlret != SQLITE_OK )
	  {
      	log_error("sqlite select idmax error: %s\n", zErrMsg);
      	sqlite3_free(zErrMsg);
   	}
	  else
	  {
      	log_info("select idmax successfully\n");
        return  maxid ;
   	}
    
}



int sqlite_insert_data(char *buf_data[],char * table_name)       
{
    char    buf_insert[256]; 
    char   *ID_spl = buf_data[0];
	  char   *SN_spl = buf_data[10];
	  char   *TIME_spl = buf_data[20];
	  char   *TEMP_spl = buf_data[30];
     
    log_info("SPLID:%s,SPLSN:%s,SPLtime:%s,SPLtemp:%s\n",ID_spl,SN_spl,TIME_spl,TEMP_spl);
    
    
    memset(buf_insert,0,sizeof(buf_insert));
    
    snprintf(buf_insert,sizeof(buf_insert),"INSERT INTO %s VALUES ('%s', '%s', '%s', '%s');",table_name,ID_spl,SN_spl,TIME_spl,TEMP_spl );
    sqlret=sqlite3_exec(db, buf_insert, callback, 0, &zErrMsg);
   	if( sqlret != SQLITE_OK )
	  {
      	printf("sqlite inset table error: %s\n", zErrMsg);
      	sqlite3_free(zErrMsg);
   	}
	  else
	  {
      	log_info("Last data inset table successfully\n");
   	}
	  return 0;
}

char * sqlite_getdata(char * table_name,int i)   
{
    char   buf_getdata[128];
    

    memset(buf_getdata,0,sizeof(buf_getdata));
    snprintf(buf_getdata,sizeof(buf_getdata),"select * from %s limit 1 offset %d;",table_name,i);
    sqlret=sqlite3_exec(db,buf_getdata,sqlite_getdata_callback,0,&zErrMsg);
    
    if( sqlret != SQLITE_OK )
   	{
      	log_error("get data from sqlite error: %s\n", zErrMsg);
     	  sqlite3_free(zErrMsg);
  	}
  	else
   	{
		    return buf_send;
   	}
}



int sqlite_deldata(char * table_name,int i)       
{
    char   buf_deldata[128];
   
    memset(buf_deldata,0,sizeof(buf_deldata));
    snprintf(buf_deldata,sizeof(buf_deldata),"DELETE from %s where ID=%d;SELECT * from %s",table_name,i,table_name);
    sqlret=sqlite3_exec(db,buf_deldata,0,0,&zErrMsg);
    
    if( sqlret != SQLITE_OK )
   	{
        log_error("delete data from sqlite error: %s\n", zErrMsg);
     	  sqlite3_free(zErrMsg);
  	}
    log_info("delete successful\n");
    return 0 ;
}

int sqlite_close_db(void)
{
    sqlret=sqlite3_close(db);
    
    if( sqlret != SQLITE_OK )
    {
        log_error("Error close database: %s\n", sqlite3_errmsg(db));
        sqlite3_free(zErrMsg);
    }
    else
    {
        log_info("successfully close database\n");
    }
    return 0;
}